Dynamic SGA Tuning of Oracle Database on Oracle Solaris with DISM

1 Introduction Oracle Database has matured over many years, and current releases support a rich set of features that greatly expand database functionality and improve performance. Fortunately, increased functionality has not led to greater complexity – each new major release of Oracle Database has featured fewer tunables thanks to automatic tuning of database instances. Management of the System Global Area (SGA) illustrates these improvements. Recent releases of Oracle Database can automatically assign more memory, up to a prescribed limit, to both the buffer cache and the shared pool if additional memory will improve performance. The Oracle Solaris Operating System supports such operations with the Dynamic Intimate Shared Memory (DISM) feature. This whitepaper describes the purpose of DISM, its function, the steps necessary to ensure its effective behavior, and potential pitfalls that should be avoided. It is important to note that significant performance degradation can occur if DISM is not configured correctly, and for that reason Oracle recommends that DISM be turned off by default on SPARC-based servers. For users who need the functionality provided by DISM, this document should help identify how to configure and monitor DISM to ensure its correct behavior. Oracle recommends that DISM should always be turned off on x86-based systems running Oracle Solaris 10. DISM is turned on by default for Oracle Database 11.2.0.1 on Oracle Solaris, which makes it important for Database Administrators (DBAs) to understand its capabilities and behavior. The MEMORY_TARGET parameter and its implications are discussed later in this paper.. There is usually considerable commonality in the tasks undertaken by users connected to the same database instance, and users running transaction-based workloads in particular frequently access many of the same data blocks. For this reason, Oracle Database keeps frequently used data blocks in a cache in memory called the Buffer Cache, and shares other frequently accessed information, such as table metadata and parsed (processed) SQL statements, in a second memory cache called the Shared Pool. These memory caches are held in a single shared memory to allow multiple users to access them concurrently. Shared memory also facilitates interprocess communication.